package com.example.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.fastjson.JSON;
import com.example.dto.*;
import com.example.service.SqlHelper;
import com.example.vo.SysResult;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@RestController
@CrossOrigin
public class Controller {

    @Value("${cust.id}")
    private String custID;

    @Autowired
    SqlHelper sqlHelper;

    @RequestMapping("/getUrl")
    public SysResult getURL(@RequestParam("custId") String custId,
                            @RequestParam("projectId") String projectId) {


        System.out.println("接收到参数客户id:" + custId + ",项目id" + projectId);

        boolean contains = custID.contains(custId);
        if (!contains) return SysResult.fail("查询客户不存在,请联系管理员处理!!!");


        ExcelWriter excelWriter = null;
        String fileName = new SimpleDateFormat("MMddHHmmss").format(new Date()) + custId + projectId;

        String lcsql = "select p.projectname name,ifnull(c.ContractAmount,0) amount,ifnull(c.WarrantyAmount,0) zbAmount,c.objectId objectId from i_D277451emProject p ";
        lcsql = lcsql + "left join i_D277451emContract c on p.objectId = c.projectId where p.objectId = '" + projectId + "' ";
        String jsonStr = sqlHelper.getValue(lcsql, custId);

        lcsql = "call get_coInfo('" + projectId + "')";
        sqlHelper.Execute(lcsql, custId);
        lcsql = "call get_CLPayInfo('" + projectId + "')";
        sqlHelper.Execute(lcsql, custId);
        lcsql = "call get_LWPayInfo('" + projectId + "')";
        sqlHelper.Execute(lcsql, custId);
        lcsql = "call p_CalcTotal('" + projectId + "')";
        sqlHelper.Execute(lcsql, custId);


        try {
//        /**
//         * 项目名称
//         * */
            JSONObject project = new JSONObject(jsonStr);
            String count = project.getString("count");
            if (count.equals("0")) return SysResult.fail("项目ID有误,并未查询到相关项目!!!");
            JSONArray projectJSONArray = project.getJSONArray("data");
            String objectId = projectJSONArray.getJSONObject(0).getString("objectid");
            if (objectId == null || objectId.isEmpty()) return SysResult.fail("该项目并未创建主合同,请选择其它项目查看!!!");

            String projectName = projectJSONArray.getJSONObject(0).getString("name");
            String templatePath = "/data/project/java/sy/template.xlsx";
            String filePath = "/data/project/java/sy/item/" + fileName + "";
            File file = new File(filePath);
            if (!file.exists()) {
                boolean dr = file.mkdirs();
                if (!dr) return SysResult.fail("文件目录创建失败!!!");
            }
            filePath = filePath + "/" + projectName + ".xlsx";

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(filePath).withTemplate(templatePath);
            excelWriterBuilder.inMemory(Boolean.TRUE);
            excelWriter = excelWriterBuilder.build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

            List<Project> projects = JSON.parseArray(projectJSONArray.toString(), Project.class);
            excelWriter.fill(projects, writeSheet);
            System.out.println("项目数据填充完毕!!!");

            /**
             * 项目收款明细
             * */
            lcsql = "select * from t_coInfo";
            jsonStr = sqlHelper.getValue(lcsql, custId);
            JSONObject coItem = new JSONObject(jsonStr);
            JSONArray itemJSONArray = coItem.getJSONArray("data");


            List<CollectionItem> rowItem = JSON.parseArray(itemJSONArray.toString(), CollectionItem.class);
            excelWriter.fill(new FillWrapper("ro", rowItem), fillConfig, writeSheet);
            System.out.println("收款明细数据填充完毕!!!");

            Total total = new Total();
            for (int i = 0; i < itemJSONArray.length(); i++) {
                JSONObject jsonObject = itemJSONArray.getJSONObject(i);
                double dkAmount = Double.parseDouble(jsonObject.getString("dkamount"));
                double invoiceAmount = Double.parseDouble(jsonObject.getString("invoiceamount"));
                double coPayAmount = Double.parseDouble(jsonObject.getString("collamount"));
                double coFileAmount = Double.parseDouble(jsonObject.getString("fileamount"));
                double coUnCollection = Double.parseDouble(jsonObject.getString("uncollection"));
//                System.out.println("dkamount:'" + dkAmount + "',invoiceAmount:'" + invoiceAmount + "',coPayAmount:'" + coPayAmount + "',coFileAmount:'" + coFileAmount + "',coUnCollection:'" + coUnCollection + "'");
                total.setTotal(total.getTotal() + coPayAmount);
                total.setTotalDK(total.getTotalDK() + dkAmount);
                total.setTotalKP(total.getTotalKP() + invoiceAmount);
                total.setTotalDZ(total.getTotalDZ() + coPayAmount);
                total.setTotalZL(total.getTotalZL() + coFileAmount);
                total.setTotalUN(total.getTotalUN() + coUnCollection);
            }
            excelWriter.fill(total, writeSheet);
            System.out.println("收款汇总完毕!!!");

            /**
             * 材料付款项目明细
             * */

            lcsql = "select * from t_clInfo";
            jsonStr = sqlHelper.getValue(lcsql, custId);
            JSONObject clPayItem = new JSONObject(jsonStr);
            JSONArray clPayItemJSONArray = clPayItem.getJSONArray("data");

            List<CLPaymentItem> CLPaymentItem = JSON.parseArray(clPayItemJSONArray.toString(), CLPaymentItem.class);
            excelWriter.fill(new FillWrapper("cl", CLPaymentItem), fillConfig, writeSheet);
            System.out.println("材料付款数据填充完毕!!!");

            ClPayTotal clPayTotal = new ClPayTotal();
            for (int i = 0; i < clPayItemJSONArray.length(); i++) {
                JSONObject jsonObject = clPayItemJSONArray.getJSONObject(i);
                String dkamount = jsonObject.getString("dkamount");
                String invoiceamount = jsonObject.getString("invoiceamount");
                String payamount = jsonObject.getString("payamount");
                String fileamount = jsonObject.getString("fileamount");
                String unpayment = jsonObject.getString("unpayment");
                clPayTotal.setClPayTotal(clPayTotal.getClPayTotal() + Double.parseDouble(StringUtils.isEmpty(payamount) ? "0" : payamount));
                clPayTotal.setClPayTotalDK(clPayTotal.getClPayTotalDK() + Double.parseDouble(StringUtils.isEmpty(dkamount) ? "0" : dkamount));
                clPayTotal.setClPayTotalKP(clPayTotal.getClPayTotalKP() + Double.parseDouble(StringUtils.isEmpty(invoiceamount) ? "0" : invoiceamount));
                clPayTotal.setClPayTotalFK(clPayTotal.getClPayTotalFK() + Double.parseDouble(StringUtils.isEmpty(payamount) ? "0" : payamount));
                clPayTotal.setClPayTotalZL(clPayTotal.getClPayTotalZL() + Double.parseDouble(StringUtils.isEmpty(fileamount) ? "0" : fileamount));
                clPayTotal.setClTotalUnPay(clPayTotal.getClTotalUnPay() + Double.parseDouble(StringUtils.isEmpty(unpayment) ? "0" : unpayment));
//                System.out.println("嘿嘿");
            }
            excelWriter.fill(clPayTotal, writeSheet);
            System.out.println("付款汇总完毕!!!");

            /**
             * 劳务付款项目明细
             * */

            lcsql = "select * from t_lwInfo";
            jsonStr = sqlHelper.getValue(lcsql, custId);
            JSONObject lwPayItem = new JSONObject(jsonStr);
            JSONArray lwPayItemJSONArray = lwPayItem.getJSONArray("data");

            List<LWPaymentItem> LWPaymentItem = JSON.parseArray(lwPayItemJSONArray.toString(), LWPaymentItem.class);
            excelWriter.fill(new FillWrapper("lw", LWPaymentItem), fillConfig, writeSheet);
            System.out.println("劳务付款数据填充完毕!!!");

            LwPayTotal lwClPayTotal = new LwPayTotal();
            for (int i = 0; i < lwPayItemJSONArray.length(); i++) {
                JSONObject jsonObject = lwPayItemJSONArray.getJSONObject(i);
                String dkamount = jsonObject.getString("dkamount");
                String invoiceamount = jsonObject.getString("invoiceamount");
                String payamount = jsonObject.getString("payamount");
                String fileamount = jsonObject.getString("fileamount");
                String unpayment = jsonObject.getString("unpayment");
                lwClPayTotal.setLwPayTotal(lwClPayTotal.getLwPayTotal() + Double.parseDouble(StringUtils.isEmpty(payamount) ? "0" : payamount));
                lwClPayTotal.setLwPayTotalDK(lwClPayTotal.getLwPayTotalDK() + Double.parseDouble(StringUtils.isEmpty(dkamount) ? "0" : dkamount));
                lwClPayTotal.setLwPayTotalKP(lwClPayTotal.getLwPayTotalKP() + Double.parseDouble(StringUtils.isEmpty(invoiceamount) ? "0" : invoiceamount));
                lwClPayTotal.setLwPayTotalFK(lwClPayTotal.getLwPayTotalFK() + Double.parseDouble(StringUtils.isEmpty(payamount) ? "0" : payamount));
                lwClPayTotal.setLwPayTotalZL(lwClPayTotal.getLwPayTotalZL() + Double.parseDouble(StringUtils.isEmpty(fileamount) ? "0" : fileamount));
                lwClPayTotal.setLwTotalUnPay(lwClPayTotal.getLwTotalUnPay() + Double.parseDouble(StringUtils.isEmpty(unpayment) ? "0" : unpayment));
            }
            excelWriter.fill(lwClPayTotal, writeSheet);
            System.out.println("劳务汇总完毕!!!");


            /**
             * 项目汇总合计
             * */
            lcsql = "select * from t_totalInfo";
            jsonStr = sqlHelper.getValue(lcsql, custId);
            JSONObject finalTotal = new JSONObject(jsonStr);
            JSONArray totalJSONArray = finalTotal.getJSONArray("data");
            List<FinalTotal> finalTotalList = JSON.parseArray(totalJSONArray.toString(), FinalTotal.class);
            excelWriter.fill(finalTotalList, fillConfig, writeSheet);
            System.out.println("所有数据汇总完毕!!!!");

            filePath = "http://117.72.73.152:20241/excel"+filePath;
            return SysResult.success(filePath);

        } catch (Exception e) {
            e.printStackTrace();
            return SysResult.fail("后端数据处理失败,请联系管理人员!!!");

        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }


}
